package com.scuec.groupfive.mapper;

import com.scuec.groupfive.entity.ExamStatistics;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;

@Mapper
public interface ExamStatisticsMapper {

    // 按天统计
    @Select("SELECT " +
            "DATE(exam_date) AS time_period, " +
            "COUNT(DISTINCT exam_id) AS exam_count, " +
            "ROUND(correct_questions * 100.0 / 16, 2) AS avg_correct_rate " +
            "FROM ( " +
            "   SELECT exam_id, exam_date, correct_questions FROM mock_exams " +
            "   UNION ALL " +
            "   SELECT record_id AS exam_id, exam_date, correct_questions FROM past_exam_records " +
            ") AS combined_exams " +
            "GROUP BY DATE(exam_date) " +
            "ORDER BY time_period")
    List<ExamStatistics> getDailyStatistics();

    // 按周统计
    @Select("SELECT " +
            "DATE_FORMAT(exam_date, '%Y-%u') AS time_period, " +
            "COUNT(DISTINCT exam_id) AS exam_count, " +
            "ROUND(SUM(correct_questions) * 100.0 / (COUNT(DISTINCT exam_id) * 16), 2) AS avg_correct_rate " +
            "FROM ( " +
            "   SELECT exam_id, exam_date, correct_questions FROM mock_exams " +
            "   UNION ALL " +
            "   SELECT record_id AS exam_id, exam_date, correct_questions FROM past_exam_records " +
            ") AS combined_exams " +
            "GROUP BY DATE_FORMAT(exam_date, '%Y-%u') " +
            "ORDER BY time_period")
    List<ExamStatistics> getWeeklyStatistics();

    // 按月统计
    @Select("SELECT " +
            "DATE_FORMAT(exam_date, '%Y-%m') AS time_period, " +
            "COUNT(DISTINCT exam_id) AS exam_count, " +
            "ROUND(SUM(correct_questions) * 100.0 / (COUNT(DISTINCT exam_id) * 16), 2) AS avg_correct_rate " +
            "FROM ( " +
            "   SELECT exam_id, exam_date, correct_questions FROM mock_exams " +
            "   UNION ALL " +
            "   SELECT record_id AS exam_id, exam_date, correct_questions FROM past_exam_records " +
            ") AS combined_exams " +
            "GROUP BY DATE_FORMAT(exam_date, '%Y-%m') " +
            "ORDER BY time_period")
    List<ExamStatistics> getMonthlyStatistics();

    // 按年统计
    @Select("SELECT " +
            "YEAR(exam_date) AS time_period, " +
            "COUNT(DISTINCT exam_id) AS exam_count, " +
            "ROUND(SUM(correct_questions) * 100.0 / (COUNT(DISTINCT exam_id) * 16), 2) AS avg_correct_rate " +
            "FROM ( " +
            "   SELECT exam_id, exam_date, correct_questions FROM mock_exams " +
            "   UNION ALL " +
            "   SELECT record_id AS exam_id, exam_date, correct_questions FROM past_exam_records " +
            ") AS combined_exams " +
            "GROUP BY YEAR(exam_date) " +
            "ORDER BY time_period")
    List<ExamStatistics> getYearlyStatistics();
}